insert overwrite table jms_dm.dm_duration_of_whole_process_pre_mt
select
      trunc(sign_date,'MM') as monday_date
     ,substr(sign_date,1,7) as sign_date          --时效签收日期'
     ,send_network_code                      as send_network_code       --始发网点编码
     ,send_network_name                      as send_network_name       --始发网点名称
     ,sign_network_code                      as sign_network_code       --签收网点编码
     ,sign_network_name                      as sign_network_name       --签收网点名称
     ,send_area_id                           as send_area_id            --始发区县id
     ,send_area_desc                         as send_area_desc          --始发区县名称
     ,send_city_id                           as send_city_id            --始发城市id
     ,send_city_desc                         as send_city_desc          --始发城市名称
     ,send_agent_code                        as send_agent_code         --始发代理区code
     ,send_agent_name                        as send_agent_name         --始发代理区名称
     ,send_fran_code                         as send_fran_code          --始发加盟商code
     ,send_fran_name                         as send_fran_name          --始发加盟商名称
     ,send_provider_id                       as send_provider_id        --始发省份id
     ,send_provider_desc                     as send_provider_desc      --始发省份名称
     ,send_regional_id                       as send_regional_id        --始发大区id
     ,send_regional_desc                     as send_regional_desc      --始发大区名称
     ,sign_area_id                           as sign_area_id            --签收区县id
     ,sign_area_desc                         as sign_area_desc          --签收区县名称
     ,sign_city_id                           as sign_city_id            --签收城市id
     ,sign_city_desc                         as sign_city_desc          --签收城市名称
     ,sign_agent_code                        as sign_agent_code         --签收代理区code
     ,sign_agent_name                        as sign_agent_name         --签收代理区名称
     ,sign_fran_code                         as sign_fran_code          --签收加盟商code
     ,sign_fran_name                         as sign_fran_name          --签收加盟商名称
     ,sign_provider_id                       as sign_provider_id        --签收省份id
     ,sign_provider_desc                     as sign_provider_desc      --签收省份名称
     ,sign_regional_id                       as sign_regional_id        --签收大区id
     ,sign_regional_desc                     as sign_regional_desc      --签收大区名称
     ,order_source_id                        as order_source_id         --订单来源id
     ,ordersource_code                       as ordersource_code        --订单来源编码
     ,ordersource_name                       as ordersource_name        --订单来源名称
     ,deliver_later_user_code                as deliver_later_user_code --出仓员编码 
     ,deliver_later_user                     as deliver_later_user      --出仓员名称
     ,sum(pre_sign_taking_time             ) as pre_sign_taking_time
     ,sum(real_sign_taking_time            ) as real_sign_taking_time
     ,sum(first_center_taking_time         ) as first_center_taking_time
     ,sum(inout_first_center_time          ) as inout_first_center_time
     ,sum(dest_first_center_time           ) as dest_first_center_time
     ,sum(last_first_center_time           ) as last_first_center_time
     ,sum(inout_last_center_time           ) as inout_last_center_time
     ,sum(dest_last_center_time            ) as dest_last_center_time
     ,sum(pre_sign_dest_time               ) as pre_sign_dest_time
     ,sum(real_pre_sign_time               ) as real_pre_sign_time
     ,sum(network_taking_send_time         ) as network_taking_send_time
     ,sum(network_send_nodal_arrival_time  ) as network_send_nodal_arrival_time
     ,sum(first_nodal_arrival_send_time    ) as first_nodal_arrival_send_time
     ,sum(nodal_send_center_arrival_time   ) as nodal_send_center_arrival_time
     ,sum(nodal_arrival_center_send_time   ) as nodal_arrival_center_send_time
     ,sum(end_nodal_arrival_send_time      ) as end_nodal_arrival_send_time
     ,sum(nodal_send_network_arrival_time  ) as nodal_send_network_arrival_time
     ,sum(end_network_arrival_deliver_time ) as end_network_arrival_deliver_time
     ,sum(deliver_aging_sign_time          ) as deliver_aging_sign_time
     ,sum(actual_aging_sign_time           ) as actual_aging_sign_time
     ,sum(pre_sign_taking_cnt              ) as pre_sign_taking_cnt
     ,sum(real_sign_taking_cnt             ) as real_sign_taking_cnt
     ,sum(first_center_taking_cnt          ) as first_center_taking_cnt
     ,sum(inout_first_center_cnt           ) as inout_first_center_cnt
     ,sum(dest_first_center_cnt            ) as dest_first_center_cnt
     ,sum(last_first_center_cnt            ) as last_first_center_cnt
     ,sum(inout_last_center_cnt            ) as inout_last_center_cnt
     ,sum(dest_last_center_cnt             ) as dest_last_center_cnt
     ,sum(pre_sign_dest_cnt                ) as pre_sign_dest_cnt
     ,sum(real_pre_sign_cnt                ) as real_pre_sign_cnt
     ,sum(network_taking_send_cnt          ) as network_taking_send_cnt
     ,sum(network_send_nodal_arrival_cnt   ) as network_send_nodal_arrival_cnt
     ,sum(first_nodal_arrival_send_cnt     ) as first_nodal_arrival_send_cnt
     ,sum(nodal_send_center_arrival_cnt    ) as nodal_send_center_arrival_cnt
     ,sum(nodal_arrival_center_send_cnt    ) as nodal_arrival_center_send_cnt
     ,sum(end_nodal_arrival_send_cnt       ) as end_nodal_arrival_send_cnt
     ,sum(nodal_send_network_arrival_cnt   ) as nodal_send_network_arrival_cnt
     ,sum(end_network_arrival_deliver_cnt  ) as end_network_arrival_deliver_cnt
     ,sum(deliver_aging_sign_cnt           ) as deliver_aging_sign_cnt
     ,sum(actual_aging_sign_cnt            ) as actual_aging_sign_cnt
     ,sum(if_deliver_24h_cnt               ) as if_deliver_24h_cnt  --派件-时效签收超24H量
     ,substr(sign_date,1,7)   as dt
from jms_dm.dm_duration_of_whole_process_pre_dt
where dt >=trunc(date_add('{{ execution_date | cst_ds }}',-day('{{ execution_date | cst_ds }}')),'MM')
  and dt <= '{{ execution_date | cst_ds }}'
group by trunc(sign_date,'MM')
       ,substr(sign_date,1,7)                       --时效签收日期'
       ,send_network_code                           --始发网点编码'
       ,send_network_name                           --始发网点名称'
       ,sign_network_code                           --签收网点编码'
       ,sign_network_name                           --签收网点名称'
       ,order_source_id                             --订单来源id'
       ,send_area_id                                --始发区县id'
       ,send_area_desc                              --始发区县名称'
       ,send_city_id                                --始发城市id'
       ,send_city_desc                              --始发城市名称'
       ,send_agent_code                             --始发代理区code'
       ,send_agent_name                             --始发代理区名称'
       ,send_fran_code                              --始发加盟商code'
       ,send_fran_name                              --始发加盟商名称'
       ,send_provider_id                            --始发省份id'
       ,send_provider_desc                          --始发省份名称'
       ,send_regional_id                            --始发大区id'
       ,send_regional_desc                          --始发大区名称'
       ,sign_area_id                                --签收区县id'
       ,sign_area_desc                              --签收区县名称'
       ,sign_city_id                                --签收城市id'
       ,sign_city_desc                              --签收城市名称'
       ,sign_agent_code                             --签收代理区code'
       ,sign_agent_name                             --签收代理区名称'
       ,sign_fran_code                              --签收加盟商code'
       ,sign_fran_name                              --签收加盟商名称'
       ,sign_provider_id                            --签收省份id'
       ,sign_provider_desc                          --签收省份名称'
       ,sign_regional_id                            --签收大区id'
       ,sign_regional_desc                          --签收大区名称'
       ,ordersource_code                            --多平台订单来源编码'
       ,ordersource_name                            --多平台订单来源名称'
       ,deliver_later_user_code                     --最晚出仓员编码'
       ,deliver_later_user                          --最晚出仓员名称'
       ,substr(sign_date,1,7)
distribute by substr(sign_date,1,7),pmod(hash(rand()),60)
;
        
        
        
        
        
        

